﻿using System;

public class SqlPager
{
    private int recordCount;

    /// <summary>
    /// 数据条数
    /// </summary>
    public int RecordCount
    {
        get { return recordCount; }
    }

    private int pageIndex;

    /// <summary>
    /// 页号
    /// </summary>
    public int PageIndex
    {
        get { return pageIndex; }
        set { pageIndex = value; }
    }

    private int pageSize;

    /// <summary>
    /// 每页数据条数
    /// </summary>
    public int PageSize
    {
        get { return pageSize; }
        set { pageSize = value; }
    }

    private int pageCount;

    /// <summary>
    /// 页数
    /// </summary>
    public int PageCount
    {
        get { return pageCount; }
    }

    private string identityField = "ID";

    /// <summary>
    /// 主键字段名称
    /// </summary>
    public string IdentityField
    {
        get { return identityField; }
        set { identityField = value; }
    }

    private string fieldNames = "*";

    /// <summary>
    /// 查询的字段
    /// </summary>
    public string FieldNames
    {
        get { return fieldNames; }
        set { fieldNames = value; }
    }

    private string tableName;

    /// <summary>
    /// 表名
    /// </summary>
    public string TableName
    {
        get { return tableName; }
        set { tableName = value; }
    }

    private string where;

    /// <summary>
    /// 条件
    /// </summary>
    public string Where
    {
        get { return where; }
        set { where = value; }
    }

    private string orderBy;

    /// <summary>
    /// 排序
    /// </summary>
    public string OrderBy
    {
        get { return orderBy; }
        set { orderBy = value; }
    }
    
    public SqlPager(string tableName)
    {
        this.tableName = tableName;
    }

    public SqlPager(string tableName, string identityField, string fieldNames)
    {
        this.tableName = tableName;
        this.identityField = identityField;
        this.fieldNames = fieldNames;
    }

    /// <summary>
    /// 生成分页sql
    /// </summary>
    /// <returns></returns>
    public string GetPagedSql()
    {
        string pWhere = string.Empty;
        if (!string.IsNullOrEmpty(Where))
        {
            pWhere = " where " + Where;
        }
        string pOrder = string.Empty;
        if (!string.IsNullOrEmpty(OrderBy))
        {
            pOrder = " order by " + OrderBy;
        }

        recordCount = Convert.ToInt32(SqlHelper.Instance.ExecuteScalar("select count(1) from " + TableName + pWhere));
        int residue = RecordCount % PageSize;
        pageCount = RecordCount / PageSize;
        if (residue > 0)
            pageCount += 1;

        if (PageIndex <= 1)
        {
            return string.Format("SELECT TOP {0} {1} FROM {2} {3} {4}", PageSize, FieldNames, TableName, pWhere, pOrder);
        }
        if (PageIndex > PageCount)
            PageIndex = PageCount;

        if (string.IsNullOrEmpty(Where))
        {
            return string.Format("SELECT TOP {0} * FROM (SELECT TOP {0} {2} FROM {3} WHERE {4} NOT IN (SELECT TOP {1} {4} FROM {3} {5}) {5}) as [{3}] {5}", PageSize, PageSize * (PageIndex - 1), FieldNames, TableName, IdentityField, pOrder);
        }

        return string.Format("SELECT TOP {0} * FROM (SELECT TOP {0} {2} FROM {3} {6} and {4} NOT IN (SELECT TOP {1} {4} FROM {3} {6} {5}) {5}) as [{3}] {5}", PageSize, PageSize * (PageIndex - 1), FieldNames, TableName, IdentityField, pOrder, pWhere);
    }
}